import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.net.URISyntaxException;
import java.net.URL;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.LinkedList;
import java.util.logging.Logger;

public class Main {

  /**
   * @param args
   * @throws SQLException 
   * @throws URISyntaxException 
   */
  public static void main(String[] args) throws SQLException, URISyntaxException {
    
    // IST AUSKOMMENTIERT, DA ES NUR EINMAL GEMACHT WERDEN MUSS.
    // performETL();
    
	  showMainMenu();
  }
  
  public static void flush2Log(PreparedStatement pstmt) {
    flush2Log(pstmt, null);
  }
  
  public static void flush2Log(PreparedStatement pstmt, String message) {
    if(message != null) {
      System.out.println(message);
    }
    try {
      pstmt.executeBatch();
    } catch (SQLException e) {
      e.printStackTrace();
    }
  }
  
  public static void printCrosstable( String location_, String timespan_ ) throws SQLException {
	    Connection con = DB2ConnectionManager.getInstance().getConnection();
	    
	    String selectSQL =
	    		"SELECT p."+location_+" AS location, YEAR(f.datum) AS year, "+timespan_+"(f.datum) AS timespan, " +
	    		"       f.article AS article, SUM(f.quantity) as sold " +
	    		"FROM FactSales AS f, DimPlace AS p " +
	    		"WHERE f.shop=p.shop " +
	    		"GROUP BY Grouping Sets (" +
	    		"    (p."+location_+", YEAR(f.datum), "+timespan_+"(f.datum), f.article)," +
	    		"    (p."+location_+", YEAR(f.datum), "+timespan_+"(f.datum))," + // alle Verkäufe einer Location im Zeitraum
	    		"    (p."+location_+", f.article)," +                             // Verkäufe bestimmter Artikel einer Location
	    		"     p."+location_+"," +                                         // alle Verkäufe einer Location
	    		"     f.article" +                                                // alle Verkäufe eines Artikels
	    		"  ) " +
	    		"ORDER BY location, year, timespan, article";
	    PreparedStatement pstmt = con.prepareStatement(selectSQL);
	    ResultSet rs = pstmt.executeQuery();
	    
	    String curLocation  = "";
	    String curTimespan = "";
	    Boolean locationChanged = false;
	    Boolean timespanChanged = false;
	    Boolean lastTotal = true;
	    
	    int seplen = 0;
	    
		// Header ausgeben
    	System.out.print( String.format("%1$-23s", "Region") + "| Zeitraum | ");
    	seplen += 36;
    	
	    while(rs.next()) {
	    	if( rs.getString(1) == null && rs.getString(2) == null && rs.getString(3) == null ) {
	    		if( rs.getString(4) != null ) { 
	    			System.out.print( rs.getString(4).substring(0,10) + " | " );
	    			seplen += 13;
	    		}
	    		else
	    			break;
	    	}
	    }
    	
	    System.out.print( "Total      |" );
		seplen += 12;
	    
	    rs = pstmt.executeQuery();
    	
	    while(rs.next()) {
	    	// Bei neuer Region
	    	if( rs.getString(1) != null && !curLocation.equals(rs.getString(1))) {
	    		System.out.println();
	    		for( int i = 0; i < seplen; ++i )
	    		  System.out.print( "-" );
	    		System.out.println();
	    		curLocation = rs.getString(1);
	    		locationChanged = true;
	    	}
	    	else if( rs.getString(1) == null && lastTotal ) {
	    		System.out.println();
	    		for( int i = 0; i < seplen; ++i )
		    		  System.out.print( "-" );
	    		System.out.println();
	    		curLocation = "Total";
	    		locationChanged = true;
	    		lastTotal = false;
	        }
	    	
	    	// Bei neuem Quartal
	    	if( !curTimespan.equals(rs.getString(3)+"/"+rs.getString(2))) {
	    		curTimespan = rs.getString(3)+"/"+rs.getString(2);
	    		timespanChanged = true;
	    	}
	    	
	    	if( locationChanged || timespanChanged ) {
	    		String location = locationChanged ? curLocation : "";
	    		String timespan = "";
	    		if( curTimespan.equals("null/null") )
	    			timespan = "Total";
	    		else if( timespan_.equals("YEAR") )
	    			timespan = rs.getString(3);
	    		else
	    			timespan = curTimespan;
	    		
	    		System.out.print( String.format("%1$-23s", location) + "| " + String.format("%1$8s", timespan) + " | " );
	    	}
		    	
	    	
	    	System.out.print( String.format("%1$-10s", rs.getString(5)) + " | " );
	    	
	    	// Bei neuem Quartal
	    	if( rs.getString(4) == null ) {
	    		System.out.println();
	    	}
	    	
		    locationChanged = false;
		    timespanChanged = false;
	    }
	    
	    System.out.println();
	    System.out.println();
  }
  
  public static String showLocationMenu() throws SQLException {
		//Menüoptionen
		final int MENU_SHOP    = 0;
		final int MENU_CITY    = 1;
		final int MENU_REGION  = 2;
		final int MENU_COUNTRY = 3;
		final int QUIT = -1;
		
		//Erzeuge Menü
		Menu mainMenu = new Menu("Ort");
		mainMenu.addEntry("Laden",  MENU_SHOP);
		mainMenu.addEntry("Stadt",  MENU_CITY);
		mainMenu.addEntry("Region", MENU_REGION);
		mainMenu.addEntry("Land",   MENU_COUNTRY);
		mainMenu.addEntry("Zurück", QUIT);
		
		//Verarbeite Eingabe
		while(true) {
			int response = mainMenu.show();
			
			switch(response) {
			case MENU_SHOP:
				return "shop";
			case MENU_CITY:
				return "city";
			case MENU_REGION:
				return "region";
			case MENU_COUNTRY:
				return "country";
				case QUIT:
					return null;
			}
		}
  }
  
  public static String showTimespanMenu() throws SQLException {
		//Menüoptionen
		final int MENU_DAY     = 0;
		final int MENU_WEEKDAY = 5;
		final int MENU_WEEK    = 1;
		final int MENU_MONTH   = 2;
		final int MENU_QUARTER = 3;
		final int MENU_YEAR    = 4;
		final int QUIT = -1;
		
		//Erzeuge Menü
		Menu mainMenu = new Menu("Zeitraum");
		mainMenu.addEntry("Tag",       MENU_DAY);
		mainMenu.addEntry("Wochentag", MENU_WEEKDAY);
		mainMenu.addEntry("Woche",     MENU_WEEK);
		mainMenu.addEntry("Monat",     MENU_MONTH);
		mainMenu.addEntry("Quartal",   MENU_QUARTER);
		mainMenu.addEntry("Jahr",      MENU_YEAR);
		mainMenu.addEntry("Zurück", QUIT);
		
		//Verarbeite Eingabe
		while(true) {
			int response = mainMenu.show();
			
			switch(response) {
			case MENU_DAY:
				return "DAY";
			case MENU_WEEKDAY:
				return "WEEK_DAY";
			case MENU_WEEK:
				return "WEEK";
			case MENU_MONTH:
				return "MONTH";
			case MENU_QUARTER:
				return "QUARTER";
			case MENU_YEAR:
				return "YEAR";
			case QUIT:
					return null;
			}
		}
  }
  
  public static void showMainMenu() throws SQLException {
		//Menüoptionen
		final int MENU_TABLE = 0;
		final int QUIT = -1;
		
		//Erzeuge Menü
		Menu mainMenu = new Menu("Data-Warehouse");
		mainMenu.addEntry("Cross-Table", MENU_TABLE);
		mainMenu.addEntry("Beenden", QUIT);
		
		//Verarbeite Eingabe
		while(true) {
			int response = mainMenu.show();
			
			switch(response) {
				case MENU_TABLE:
					String location = showLocationMenu();
					String timespan = showTimespanMenu();
					if( location != null && timespan != null )
						printCrosstable( location, timespan );
					break;
				case QUIT:
					return;
			}
		}
  }
  
  public void performETL() throws SQLException, URISyntaxException {
    Connection con = DB2ConnectionManager.getInstance().getConnection();
    
    // extract from DB

    // commit trasaction manually
    con.setAutoCommit(false); 
    
    String selectSQL = "SELECT DB2INST1.ARTICLEID.name AS article, DB2INST1.ARTICLEID.PREIS AS price, DB2INST1.PRODUCTGROUPID.NAME AS group, DB2INST1.PRODUCTFAMILYID.NAME AS family, DB2INST1.PRODUCTCATEGORYID.NAME AS category FROM DB2INST1.ARTICLEID LEFT OUTER JOIN DB2INST1.PRODUCTGROUPID ON DB2INST1.PRODUCTGROUPID.PRODUCTGROUPID = DB2INST1.ARTICLEID.PRODUCTGROUPID LEFT OUTER JOIN DB2INST1.PRODUCTFAMILYID ON DB2INST1.PRODUCTFAMILYID.PRODUCTFAMILYID = DB2INST1.PRODUCTGROUPID.PRODUCTFAMILYID LEFT OUTER JOIN DB2INST1.PRODUCTCATEGORYID ON DB2INST1.PRODUCTCATEGORYID.PRODUCTCATEGORYID = DB2INST1.PRODUCTFAMILYID.PRODUCTCATEGORYID";
    PreparedStatement pstmt = con.prepareStatement(selectSQL);
    ResultSet rs = pstmt.executeQuery();
    
    String insertSQL = "INSERT INTO VSISP67.DIMARTICLE (ARTICLE, GROUP, FAMILY, CATEGORY) VALUES (?,?,?,?)";       
    pstmt = con.prepareStatement(insertSQL);
    
    while(rs.next()) {
      pstmt.setString(1, rs.getString("article"));
      pstmt.setString(2, rs.getString("group"));
      pstmt.setString(3, rs.getString("family"));
      pstmt.setString(4, rs.getString("category"));
      pstmt.addBatch();
    }
    
    flush2Log(pstmt);
    
    selectSQL = "SELECT DB2INST1.SHOPID.name AS shop, DB2INST1.STADTID.NAME AS city, DB2INST1.REGIONID.NAME AS region, DB2INST1.LANDID.NAME AS country FROM DB2INST1.SHOPID LEFT OUTER JOIN DB2INST1.STADTID ON DB2INST1.STADTID.STADTID = DB2INST1.SHOPID.STADTID LEFT OUTER JOIN DB2INST1.REGIONID ON DB2INST1.REGIONID.REGIONID = DB2INST1.STADTID.REGIONID LEFT OUTER JOIN DB2INST1.LANDID ON DB2INST1.LANDID.LANDID = DB2INST1.REGIONID.LANDID";
    pstmt = con.prepareStatement(selectSQL);
    rs = pstmt.executeQuery();
    
    insertSQL = "INSERT INTO VSISP67.DIMPLACE (SHOP, CITY, REGION, COUNTRY) VALUES (?,?,?,?)";       
    pstmt = con.prepareStatement(insertSQL);
    
    while(rs.next()) {
      pstmt.setString(1, rs.getString("shop"));
      pstmt.setString(2, rs.getString("city"));
      pstmt.setString(3, rs.getString("region"));
      pstmt.setString(4, rs.getString("country"));
      pstmt.addBatch();
    }
    
    flush2Log(pstmt);
        
    // extract from CSV
    
    LinkedList<String[]> csvRecords = readCSVIntoBuffer();
    
    insertSQL = "INSERT INTO VSISP67.DIMTIME (DATUM) VALUES (?)";       
    PreparedStatement pstmtForDimTimeTable = con.prepareStatement(insertSQL);
    
    insertSQL = "INSERT INTO VSISP67.FACTSALES (DATUM, SHOP, ARTICLE, QUANTITY, SALES) VALUES (?,?,?,?,?)";       
    PreparedStatement pstmtForFACTSALESTable = con.prepareStatement(insertSQL);

    System.out.println("Inserting records into DIMTIME and into FACTSALES.");
    
    int count = 0;
    for(String[] record : csvRecords) {
      
      String[] date = record[0].split("\\.");
      String formattedDate = date[2] + '-' + date[1] + '-' + date[0];
      
      // INTO DIMTIME
      pstmtForDimTimeTable.setString(1, formattedDate);
      pstmtForDimTimeTable.addBatch();
      
      // INTO FACTSALES
      pstmtForFACTSALESTable.setString(1, formattedDate);
      pstmtForFACTSALESTable.setString(2, record[1]); // SHOP
      pstmtForFACTSALESTable.setString(3, record[2]); // ARTICLE
      pstmtForFACTSALESTable.setString(4, record[3]); // QUANTITY 
      pstmtForFACTSALESTable.setString(5, record[4].replace(',', '.')); // SALES

      // System.out.println(formattedDate + ", " + record[1] + ", " + record[2] + ", " + record[3] + ", " + record[4].replace(',', '.'));
      //pstmtForFACTSALESTable.executeUpdate();
      
      pstmtForFACTSALESTable.addBatch();
      
      if(++count%1000 == 0) {
        //flush2Log(pstmtForDimTimeTable);
        flush2Log(pstmtForFACTSALESTable, "flushing 1000 records to fact table...");
        System.out.println("So far added " + count + " to FACTSALES");
      }
    }
    
    System.out.println("Done preparing records for DIMTIME and FACTSALES.");
    System.out.println("Inserting records into DIMTIME and FACTSALES.");
    
    flush2Log(pstmtForDimTimeTable);
    flush2Log(pstmtForFACTSALESTable);
    
    System.out.println("Done inserting records into DIMTIME and FACTSALES.");
    
    con.commit();
  }
  
  
  
  
  
  public static LinkedList<String[]> readCSVIntoBuffer() throws URISyntaxException {
    
    System.out.println("Starting to load records from CSV.");
    
    File csvFile = new File(ClassLoader.getSystemResource("sales.csv").toURI());
    LinkedList<String[]> records = new LinkedList<String[]>();
    BufferedReader br = null;
    int count = 0;
    
    try {
      br = new BufferedReader(new FileReader(csvFile));
      String line = br.readLine(); // skip first line as it is the header part
      while ((line = br.readLine()) != null) {
        ++count;
        records.add(line.split(";"));   
      }
   
    } catch (FileNotFoundException e) {
      e.printStackTrace();
    } catch (IOException e) {
      e.printStackTrace();
    } finally {
      if (br != null) {
        try {
          br.close();
        } catch (IOException e) {
          e.printStackTrace();
        }
      }
    }
   
    System.out.println("Done loading " + count + " entries.");
    
    return records;
    
  }

}
